package com.yaoandw.crawl.storage.dao;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import com.yaoandw.crawl.storage.DbUtil;
import com.yaoandw.lucene.Page;

public class PageDaoImpl implements PageDao {

	public List<Page> getPageList() {
		long begin = System.currentTimeMillis();
		String sql = "select * from PAGE";
		System.out.println("select sql = " + sql);
		Statement stmt = DbUtil.getStatement();
		List<Page> scList = new ArrayList<Page>();
		try {
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Page p = new Page();
				p.setUrl(rs.getString("URL"));
				p.setPageHash(rs.getString("PAGEHASH"));
				scList.add(p);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbUtil.close();
		}
		System.out.println("cost time:" + (System.currentTimeMillis() - begin)
				/ 1000);
		return scList;
	}

	public boolean createTableIfNotExists() {
		Set tables = new HashSet();
		try {
			tables = getDBTables(DbUtil.getConnection());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		if (!tables.contains("page")) {
			StringBuffer sb = new StringBuffer(
					"create table PAGE (URL VARCHAR(255) NOT NULL ,PAGEHASH VARCHAR(50) NOT NULL,PRIMARY kEY(URL))");
			execSql(sb.toString());
		}
		return true;
	}

	public void execSql(String sql) {
//		long begin = System.currentTimeMillis();
		Statement stmt = DbUtil.getStatement();
		if (stmt != null) {
			try {
				stmt.execute(sql);
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				DbUtil.close();
			}
		}
//		System.out.println("cost:" + (System.currentTimeMillis() - begin)
//				/ 1000);
	}

	private Set<String> getDBTables(Connection targetDBConn)
			throws SQLException {
		Set<String> set = new HashSet<String>();
		DatabaseMetaData dbmeta = targetDBConn.getMetaData();
		readDBTable(set, dbmeta, "TABLE", null);
		readDBTable(set, dbmeta, "VIEW", null);
		return set;
	}

	private void readDBTable(Set<String> set, DatabaseMetaData dbmeta,
			String searchCriteria, String schema) throws SQLException {
		ResultSet rs = dbmeta.getTables(null, schema, null,
				new String[] { searchCriteria });
		while (rs.next()) {
			set.add(rs.getString("TABLE_NAME").toLowerCase());
		}
	}

	public void addPage(Page page) {
		String sql = "insert into PAGE(URL,PAGEHASH) values('"+page.getUrl()+"','"+page.getPageHash()+"')";
		execSql(sql);
	}

	public void updatePage(Page page) {
		String sql = "update PAGE set PAGEHASH='"+page.getPageHash()+"' where URL='"+page.getUrl()+"'";
		execSql(sql);
	}

	public void deletePage(String url) {
		String sql = "delete from PAGE where URL='"+url+"'";
		execSql(sql);
	}

	public Page getPage(String url) {
		String sql = "select * from PAGE  where URL='"+url+"'";
		Page page = null;
		Statement stmt = DbUtil.getStatement();
		try {
			ResultSet rs = stmt.executeQuery(sql);
			if (rs.next()) {
				page = new Page();
				page.setUrl(rs.getString("URL"));
				page.setPageHash(rs.getString("PAGEHASH"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbUtil.close();
		}
		return page;
	}

	public void clearTable() {
		String sql = "truncate table PAGE";
		execSql(sql);
		
	}

}
